package group32.server;

import group32.server.RelationBuilder;
import group32.shared.Relation;
import group32.shared.Tuple;
import group32.client.PhotoService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.google.gwt.dev.util.collect.HashMap;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;

@SuppressWarnings("serial")
public class PhotoServiceImpl extends RemoteServiceServlet implements PhotoService{
	Connection connection;
	
	public static HashMap<Integer,Float> scoreTable = new HashMap<Integer,Float>();
	
	public PhotoServiceImpl() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String serverName = "fling.seas.upenn.edu:3306";
			String database = "vdurey";
			String url = "jdbc:mysql://" + serverName + "/" + database;
			String user = "vdurey";
			String password = "cis550azerty";
			
			connection = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.exit(1);
		} catch (SQLException e) {
			e.printStackTrace();
			System.exit(1);
		}
	}
	
	/**
	 * Returns the user's photos
	 */

	public Relation getUserPhotos(int userID) throws IllegalArgumentException {
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select photos.url, photos.photoID, photos.visibility, photos.description, photos.timestamp " +
					"from photos, userphoto " +
					"where photos.photoID = userphoto.photoID AND userphoto.userID = "+ userID +" "+
					"order by timestamp desc")) {			
				ResultSet rs = statement.getResultSet();
				return RelationBuilder.createRelation("R1", rs);
			}
		} catch (SQLException e) {
			System.out.println("problem here");
			e.printStackTrace();
		}
		return null;
	}
	
	public Relation getPhoto (int photoID) throws IllegalArgumentException {
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select photos.url, photos.photoID, photos.visibility, photos.description, users.firstname, users.lastname, photos.timestamp " +
					"from photos, users, userphoto " +
					"where photos.photoID = "+ photoID +" AND photos.photoID = userphoto.photoID AND userphoto.userID=users.userID ")) {			
				ResultSet rs = statement.getResultSet();
				return RelationBuilder.createRelation("Photo", rs);
			}
		} catch (SQLException e) {
			System.out.println("problem here");
			e.printStackTrace();
		}
		return null;
	}

	//To change a photo's description
	public void changeDescription (String photoID, String description){
		try {
			Statement statement = connection.createStatement();
			
			statement.executeUpdate("update photos " +
					"set description= '" + description + "' " +
					"where photos.photoID = "+ photoID+""); {				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	//Add a photo for a given user
	public void addPhoto(int userID, String url, String description, int everyone, ArrayList<Integer> circles, ArrayList<Integer> friends) throws IllegalArgumentException {
		//First, we want to find a new photoID, we will take the max plus one
		int new_photoID=0;
			try {
				Statement statement = connection.createStatement();
				
				if (statement.execute("select max(photoID) as photoID " +
						"from photos")) {
				
					ResultSet rs = statement.getResultSet();
					Relation R = RelationBuilder.createRelation("R6", rs);
					List<Tuple> L = R.getTableAsList();
					new_photoID = Integer.parseInt(L.get(0).getValue("photoID").toString())+1;								
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//Then, we try to add our new photo to the table photos
			try {
				Statement statement = connection.createStatement();			
				statement.executeUpdate("insert into photos (photoID ,url,description,visibility) " +
						"values ("+new_photoID+", '"+url+"', '"+description+"', "+everyone+")");										
			} catch (SQLException e) {
				//If the photoID is already used, try again
				addPhoto(userID, url, description, everyone, circles, friends);
				e.printStackTrace();
			}
			try {
				Statement statement = connection.createStatement();
				for (int i=0; i<circles.size();i++)
				{
					statement.executeUpdate("insert into photocirclevisibility (circleID, photoID) "+
					"values ("+circles.get(i)+", "+new_photoID+")");
				}
				for (int i=0; i<friends.size();i++){
					statement.executeUpdate("insert into photofriendsvisibility (friendsID, photoID) "+
							"values ("+friends.get(i)+", "+new_photoID+")");
				}
				statement.executeUpdate("insert into userphoto (photoID, userID) "+
						"values ("+new_photoID+", "+userID+")");
			} catch (Exception e) {
				e.printStackTrace();
			}
			
		}
	
	//Get back the user's circles
	public Relation getUserCircles(int userID) throws IllegalArgumentException {
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select circles.name, circles.cid " +
					"from usercircles, circles " +
					"where circleID = cid AND userID = "+ userID +"")) {			
				ResultSet rs = statement.getResultSet();
				return RelationBuilder.createRelation("R2", rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//Get back the user's friends
	public Relation getUserFriends(int userID) throws IllegalArgumentException {
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select users.firstname, users.lastname, users.userID " +
					"from friends, users " +
					"where users.userID = friends.friendsID AND friends.userID = "+ userID +" ")) {			
				ResultSet rs = statement.getResultSet();
				return RelationBuilder.createRelation("R3", rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//Get back the circles that can see that photo
	public Relation getPhotoCircles(int photoID) throws IllegalArgumentException {
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select photocirclevisibility.circleID " +
					"from photocirclevisibility " +
					"where photocirclevisibility.photoID = "+ photoID +" ")) {			
				ResultSet rs = statement.getResultSet();
				return RelationBuilder.createRelation("R4", rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//Get back the friends that can see that photo
	public Relation getPhotoFriends(int photoID) throws IllegalArgumentException {
		try {
			Statement statement = connection.createStatement();
			
			if (statement.execute("select photofriendsvisibility.friendsID " +
					"from photofriendsvisibility " +
					"where photofriendsvisibility.photoID = "+ photoID +" ")) {			
				ResultSet rs = statement.getResultSet();
				return RelationBuilder.createRelation("R5", rs);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	//To add a tag
	public void addTag (String tag, int photoID, int userID) throws IllegalArgumentException {
		
		String query= "INSERT INTO `vdurey`.`phototags` (`photoID`, `tag`, `userID`) VALUES ("+photoID+", '"+tag+"', "+userID+");";
		String query1= "INSERT INTO `vdurey`.`invertedtagstable` (`tag`, `photoID`) VALUES ('"+tag+"', "+photoID+");";
		try{
				Statement statement = connection.createStatement();
				statement.executeUpdate(query);
				statement.executeUpdate(query1);
					
		} catch (Exception e) {
				// TODO: handle exception
				e.printStackTrace();
			}
		String[] keys = tag.split("\\s+");
		for(int i=0;i<keys.length;i++){
		query1= "INSERT INTO `vdurey`.`invertedtagstable` (`tag`, `photoID`) VALUES ('"+keys[i]+"', "+photoID+");";
		try{
			Statement statement = connection.createStatement();
			statement.executeUpdate(query1);
				
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
			}
		}	
	}
	
	//Determine if a photo is visible or not
	public boolean isVisible (int userID, int photoID) throws IllegalArgumentException {
		//If the photo is visible from everyone, then it is visible from the user, return true
		String query="select photoID from photos where visibility = 1 AND photoID = "+photoID+" ";
		try {
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			if (!(rs==null)||rs.getFetchSize()==0){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		//If the photo is visible from the user, return true
		query = "select photoID from photofriendsvisibility where photoID = "+photoID+" AND friendsID = "+userID+" ";
		try {
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			if (!(rs==null)){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		//If the photo is visible from a circle and the user is inside that circle, then true
		query = "select photoID from photocirclevisibility, circlefriends " +
				"where photoID = "+photoID+" AND friendsID = "+userID+" AND circlefriends.circleID = photocirclevisibility.circleID";
		try {
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			if (!(rs==null)){
				return true;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		//Else return false
		return false;
	}
	
	//Rate a photo
	public void ratePhoto (int userID, int photoID, int scoreGiven) throws IllegalArgumentException{
		String query = "INSERT INTO ratings (score, userID, photoID) VALUES ("+scoreGiven+", "+userID+", "+photoID+")";
		try{
			Statement statement = connection.createStatement();
			statement.executeUpdate(query);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}				
	}
	
	//Get back the avgRating of a photo
	public float getPhotoAvgRating (int photoID) throws IllegalArgumentException{
		String query = "select photoID, avg(score) as rating from ratings where photoID = "+photoID+" ";
		float res=0;
		try{
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			Relation R = RelationBuilder.createRelation("Rate", rs);
			if (!R.isEmpty()){
				 List<Tuple> l= R.getTableAsList();
				 res =Float.parseFloat(l.get(0).getValue("rating").toString());
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return res;
	}
	
	//Compute the score of a photo depending on the user that will see that photo
	public float scorePhoto (int userID, int photoID) throws IllegalArgumentException{
		float result=0;
		//We get the avg rating
		String query = "select photoID, avg(score) as rating from ratings where photoID = "+photoID+" ";
		try{
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			Relation R = RelationBuilder.createRelation("AvgScore", rs);
			if (!R.isEmpty()){
				 List<Tuple> l= R.getTableAsList();
				 result =Float.parseFloat(l.get(0).getValue("rating").toString());
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		//We verify if the photo is coming from a friend
		query = "select photoID from userphoto, friends " +
				"where friends.userID = "+userID+" AND friends.friendsID = userphoto.userID AND userphoto.photoID = "+photoID+" ";
		try{
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			if (rs!=null){
				 Relation R = RelationBuilder.createRelation("Rate", rs);
				 List<Tuple> l= R.getTableAsList();
				 result +=2;
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		scoreTable.put(photoID, result);
		return result;
	}
	
	//Get back a sample of photos for the home page
	public Relation getPhotoSample(int userID) throws IllegalArgumentException {
		String query = "select photos.url, photos.photoID, photos.visibility, photos.description, photos.timestamp " +
				"from photos " +
				"where photos.visibility=1 " +
				"union " +
				"select T.url, T.photoID, T.visibility, T.description, T.timestamp " +
				"from " +
				"(select photos.url, photos.photoID, photos.visibility, photos.description, photos.timestamp " +
				"from photos, photofriendsvisibility " +
				"where photos.photoID = photofriendsvisibility.photoID AND friendsID = "+userID + " " +
				"union " +
				"select photos.url, photos.photoID, photos.visibility, photos.description, photos.timestamp " +
				"from photos, photocirclevisibility, circlefriends " +
				"where photos.photoID = photocirclevisibility.photoID AND friendsID = "+userID+ " AND photocirclevisibility.circleID=circlefriends.circleID" + ") as T " +
				"order by timestamp desc ";
		try {
			Statement statement = connection.createStatement();
			statement.execute(query);
			ResultSet rs = statement.getResultSet();
			Relation R = RelationBuilder.createRelation("PhotoSample", rs);
			return R;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public void deletePhoto (int userID, int photoID) throws IllegalArgumentException{
		String query1 = "delete from userphoto where photoID = "+photoID+" AND userID = "+userID;
		String query2 = "delete from photos where photoID = "+photoID;
		try {
			Statement statement = connection.createStatement();
			statement.execute(query1);
			statement.execute(query2);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
